import {DATABASE} from "../configs/config";
import ConstUtils from "./const-utils";
import { Sequelize } from "sequelize";

const cls = require('cls-hooked');
const mysql = require('mysql2');
const constUtils = new ConstUtils();

/**
 * 数据库工具
 * @see https://github.com/demopark/sequelize-docs-Zh-CN/tree/v6
 */
class DatabaseUtils {

    private static pool: any;

    static getPool() {
        if (!DatabaseUtils.pool) {
            DatabaseUtils.initPool();
        }
        return DatabaseUtils.pool;
    }

    /**
     * 初始化连接池
     */
    private static initPool = () => {
        const namespace = cls.createNamespace('mysql-cls');
        Sequelize.useCLS(namespace);

        // 120.77.152.47 | root | a123456
        DatabaseUtils.pool = new Sequelize(String(DATABASE.DB_NAME), String(DATABASE.USER), String(DATABASE.PASSWORD), {
            host: String(DATABASE.HOST),
            port: Number(DATABASE.PORT),
            dialect: 'mysql',
            pool: {
                // 最大连接数
                max: 10,
                // 最小连接数
                min: 1,
                // 连接空闲时间
                idle: 10 * 1000
            },
            // isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ,
            logging: false, // (msg) => {log.debug(msg)},
            dialectOptions: {
                // requestTimeout: 60000,
                dateStrings: true,
                typeCast: function (field: any, next: Function) {
                    if (field.type === 'TINY' && field.length === 1) {
                        // 1 = true, 0 = false
                        return (field.string() === '1');
                    }
                    return next();
                },
            },
            timezone: '+08:00' // for writing from database
        });
        // 测试是否能连接成功
        // setTimeout(async () => {
        //     try {
        //         await pool.authenticate();
        //         console.log('Connection has been established successfully.');
        //     } catch (error) {
        //         console.error('Unable to connect to the database:', error);
        //     }
        // }, 0)
    }

    /**
     * 执行SQL
     * @param sql
     * @param params
     */
    static execSql = (sql: string, params: any) => {
        return new Promise(async (resolve, reject) => {
            try {
                const pool = DatabaseUtils.getPool();
                // ref: https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/raw-queries.md
                const [results] = await pool.query(mysql.format(sql, params), {raw: true});
                resolve(results);
            } catch (e) {
                const flowId = constUtils.getRandomId(5, 10);
                const log = global.logUtils.createContext('DatabaseUtils', 'execSql');
                log.error(`[${flowId}]SQL执行错误：`, e);
                log.error(`[${flowId}]SQL名称：`, sql);
                log.error(`[${flowId}]执行参数：`, params);
                // console.log("SQL执行错误", e.original);
                reject(`[${flowId}]SQL执行失败: ${sql}`);
            }
        });
    }

    /**
     * 参数
     * @param column 列表名称，如：user_id/userId
     */
    static colTrans = (column: string) => {
        if (column.indexOf('_') !== -1) {
            // user_id => userId
            return column.split('_')
                .map((item, index) => {
                    return index === 0 ? item.toLowerCase() : item.slice(0, 1).toUpperCase() + item.slice(1).toLowerCase();
                }).join('');
        } else {
            // userId => user_id
            return column.split('')
                .map(item => item >= 'A' && item <= 'Z' ? `_${item.toLowerCase()}` : item.toLowerCase()).join('');
        }
    }

    /**
     * 动态获取SQL(仅查询)
     * @param tableName 表名
     * @param columns 该表的列名，如：user_id
     * @param where 查询条件，如：user_name
     * @returns {{params: [], sql: string}}
     */
    static getDynamicSelectObj = (tableName: string, columns: any[], where: any[]) => {
        const params: any[] = [];
        let sql = 'select ';
        // SQL主体
        columns.map((column, index) => {
            sql += `${column} as ${DatabaseUtils.colTrans(column)} ${columns.length - 1 === index ? '' : ','} `;
        });
        // 关于表名
        sql += ` from ${tableName} `;
        // where条件
        if (where && where.length > 0) {
            sql += ' where ';
            where.map(([column, value], index) => {
                sql += `${column} = ? ${where.length - 1 === index ? '' : 'and'} `;
                params.push(value);
            });
        }
        return {
            sql: sql,
            params: params,
        };
    }

    /**
     * 动态获取SQL(仅修改)
     * @param tableName 表名
     * @param columns 该表的列名，如：user_id
     * @param where 查询条件，如：user_name
     * @returns {{params: [], sql: string}}
     */
    static getDynamicUpdateObj = (tableName: string, columns: any[], where: any[]) => {
        const params: any[] = [];
        let sql = `update ${tableName} set `;
        // SQL主体
        columns.map(([column, value], index) => {
            sql += `${column} = ? ${columns.length - 1 === index ? '' : ','} `;
            params.push(value);
        });
        // where条件
        if (where && where.length > 0) {
            sql += ' where ';
            where.map(([column, value], index) => {
                sql += `${column} = ? ${where.length - 1 === index ? '' : 'and'} `;
                params.push(value);
            });
        }
        return {
            sql: sql,
            params: params,
        };
    }
}

export default DatabaseUtils;